<?php

/**********************************************************************
Copyright (C) FrontAccounting, LLC.
Released under the terms of the GNU General Public License, GPL,
as published by the Free Software Foundation, either version 3
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
//----------------------------------------------------------------------------------------

function add_sales_order(&$order)
{
    global $loc_notification, $path_to_root, $Refs;

    begin_transaction();
    hook_db_prewrite($order, $order->trans_type);
    $order_no = get_next_trans_no($order->trans_type);
    $del_date = date2sql($order->due_date);
    $order_type = 0; // this is default on new order
    $order->ship_via = 1; //// TODO: THIS HARDCODE!
    $total = $order->get_trans_total();
    $sql = "";
    if($order->customer_id == 3)
    {             //KHUSUS
        $sql = "INSERT INTO " . TB_PREF .
        "sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date,
    order_type, ship_via, deliver_to, delivery_address, contact_phone,
    freight_cost, from_stk_loc, delivery_date, payment_terms, total,acc_rek)
    VALUES (" . db_escape($order_no) . "," . db_escape($order_type) . "," .
        db_escape($order->customer_id) . ", " . db_escape($order->trans_type) . "," .
        db_escape($order->Branch) . ", " . db_escape($order->cust_ref) . "," . db_escape($order->
        reference) . "," . db_escape($order->Comments) . ",'" . date2sql($order->
        document_date) . "', " . db_escape($order->sales_type) . ", " . db_escape($order->
        ship_via) . "," . db_escape($order->deliver_to) . "," . db_escape($order->
        delivery_address) . ", " . db_escape($order->phone) . ", " . db_escape($order->
        freight_cost) . ", " . db_escape($order->Location) . ", " . db_escape($del_date) .
        "," . db_escape($order->payment) . "," . db_escape($total) . "," . db_escape($order->acc_rek) . ")";      
        
    }
    else{
    $sql = "INSERT INTO " . TB_PREF .
        "sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date,
	order_type, ship_via, deliver_to, delivery_address, contact_phone,
	freight_cost, from_stk_loc, delivery_date, payment_terms, total)
	VALUES (" . db_escape($order_no) . "," . db_escape($order_type) . "," .
        db_escape($order->customer_id) . ", " . db_escape($order->trans_type) . "," .
        db_escape($order->Branch) . ", " . db_escape($order->cust_ref) . "," . db_escape($order->
        reference) . "," . db_escape($order->Comments) . ",'" . date2sql($order->
        document_date) . "', " . db_escape($order->sales_type) . ", " . db_escape($order->
        ship_via) . "," . db_escape($order->deliver_to) . "," . db_escape($order->
        delivery_address) . ", " . db_escape($order->phone) . ", " . db_escape($order->
        freight_cost) . ", " . db_escape($order->Location) . ", " . db_escape($del_date) .
        "," . db_escape($order->payment) . "," . db_escape($total) . ")";
    }
    db_query($sql, "order Cannot be Added");

    if($order->customer_id == 3){
        $tipe = "Penjualan Khusus";
        //default akun piutang - Debet
    $dim = 0;
    $dim2 = 0;
    add_gl_trans_customer(ST_SALESORDER, $order_no, $order->document_date, $order->acc_rek,
        $dim, $dim2, $total, $order->customer_id,
        "Akun GL Faktur Penjualan Pesan tidak bisa di - insert", 0, $tipe);



        //}
    $myrow = get_company_prefs();

    //default akun penjualan - Kredit
    add_gl_trans_customer(ST_SALESORDER, $order_no, $order->document_date, $myrow["default_sales_act"],
        0, 0, -$total, $order->customer_id,
        "Akun GL Faktur Penjualan Pesan tidak bisa di - insert", 0, $tipe);

    $Refs_cust = new references();    
    $ref = $Refs->get_next(ST_PEMBAYARAN);
        $is_bank_to = is_bank_account($order->acc_rek);
        if ($is_bank_to)
        {
            add_bank_trans(ST_SALESORDER, $order_no, $is_bank_to, $ref,
                $order->document_date, $total,0, "", get_company_currency(),
                "Cannot insert a destination bank transaction");
        }
        $sql = "INSERT INTO customers_trans(ref,so_id,date_,note,amount,tipe,no_kwitansi) values(
    " . db_escape($ref) . "," . db_escape($order_no) . "," . db_escape(date2sql
        ($order->document_date)) . "," . db_escape('Penjualan Khusus') . "," . db_escape($total) . "," . db_escape('LUNAS') .
        "," . db_escape($order->reference) . ")";
    db_query($sql);
    $order_no_next = get_next_autoincrement('customers_trans');
    $Refs_cust->save(ST_PEMBAYARAN, $order_no_next, $ref);
            
    close_sales_order($order_no);
        
    }
    //$sql = "INSERT INTO uang_muka(order_no,date_dp,isLunas,total,pay1) VALUES(" .
    //        db_escape($order_no) . ", '" . date2sql($order->document_date) . "', 0, " .
    //        db_escape($total) . ", " . db_escape($order->uang_muka) . " )";
    //    db_query($sql, "The uang muka could not be added");

    //-------------------------------------------------------------------------------------------------

    //add_bank_trans(ST_SALESORDER, $order_no, '1', $order->reference, $order->
    //        document_date, $order->uang_muka, PT_CUSTOMER, $order->Branch);
    //-------------------------------------------------------------------------------------------------

    $order->trans_no = array($order_no => 0);

    if ($loc_notification == 1)
    {
        include_once ($path_to_root . "/inventory/includes/inventory_db.inc");
        $st_ids = array();
        $st_names = array();
        $st_num = array();
        $st_reorder = array();
    }
    foreach ($order->line_items as $key => $line)
    {
        if ($loc_notification == 1 && is_inventory_item($line->stock_id))
        {
            $sql = "SELECT " . TB_PREF . "loc_stock.*, " . TB_PREF .
                "locations.location_name, " . TB_PREF . "locations.email
			FROM " . TB_PREF . "loc_stock, " . TB_PREF . "locations
			WHERE " . TB_PREF . "loc_stock.loc_code=" . TB_PREF . "locations.loc_code
			AND " . TB_PREF . "loc_stock.stock_id = '" . $line->stock_id . "'
			AND " . TB_PREF . "loc_stock.loc_code = '" . $order->Location . "'";
            $res = db_query($sql, "a location could not be retreived");
            $loc = db_fetch($res);
            if ($loc['email'] != "")
            {
                $qoh = get_qoh_on_date($line->stock_id, $order->Location);
                $qoh -= get_demand_qty($line->stock_id, $order->Location);
                $qoh -= get_demand_asm_qty($line->stock_id, $order->Location);
                $qoh -= $line->quantity;
                if ($qoh < $loc['reorder_level'])
                {
                    $st_ids[] = $line->stock_id;
                    $st_names[] = $line->item_description;
                    $st_num[] = $qoh - $loc['reorder_level'];
                    $st_reorder[] = $loc['reorder_level'];
                }
            }
        }
        //-------------------------------------------------------------------------------------------------
        $sql = "INSERT INTO " . TB_PREF .
            "sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent,ref) VALUES (";

        $myref = $order->reference;
        if ($myref != "auto")
        {
            $myref = str_replace("FPP", "NP", $myref);
            $myref .= $key;
        }
        $sql .= $order_no . "," . $order->trans_type . "," . db_escape($line->stock_id) .
            ", " . db_escape($line->item_description) . ", $line->price,
		$line->quantity,
		$line->discount_percent," . db_escape($myref) . ")";
        db_query($sql, "order Details Cannot be Added");
        //-------------------------------------------------------------------------------------------------

        $row = array(
            'jenis_barang' => $line->jenis_barang,
            'color' => $line->color,
            'kombinasi' => $line->kombinasi,
            'size_ss' => $line->size_ss,
            'price_ss' => $line->price_ss,
            'size_s' => $line->size_s,
            'size_m' => $line->size_m,
            'size_l' => $line->size_l,
            'size_ml' => $line->size_ml,
            'size_xxl' => $line->size_xxl,
            'size_xxxl' => $line->size_xxxl,
            'size_3l' => $line->size_3l,
            'size_other' => $line->size_other,
            'price_s' => $line->price_s,
            'price_m' => $line->price_m,
            'price_l' => $line->price_l,
            'price_ml' => $line->price_ml,
            'price_xxl' => $line->price_xxl,
            'price_xxxl' => $line->price_xxxl,
            'price_3l' => $line->price_3l,
            'price_other' => $line->price_other,
            'sablon' => $line->sablon,
            'model_jahit' => $line->model_jahit,
            'memo' => $line->memo,
            );

        save_sales_order_item_details($myref, $row);


        //-------------------------------------------------------------------------------------------------
        // Now mark quotation line as processed
        if ($order->trans_type == ST_SALESORDER && $line->src_id)
            update_parent_line(ST_SALESORDER, $line->src_id, $line->qty_dispatched); // clear all the quote despite all or the part was ordered

    }
    //masuk gl
    //$branch_data = get_branch_accounts($order->Branch);
    //$customer = get_customer($order->customer_id);
    $date_ = $order->document_date;
    $total = 0;
    //$stock_gl_code =get_stock_gl_code($line->stock_id);
    //Post sales transaction to GL credit sales
    //foreach ($order->line_items as $line_no => $invoice_line) {
    //$sales_account = '41300'; //$stock_gl_code['sales_account'];
    //    $dim = 0;
    //    $dim2 = 0;
    //    $total += add_gl_trans_customer(ST_SALESORDER, $order_no, $date_, $sales_account,
    //        $dim, $dim2, -$order->uang_muka, $order->customer_id,
    //        "Akun GL Faktur Penjualan Pesan tidak bisa di - insert", 0, "Uang Muka");
    //    //}
    //    $total += add_gl_trans_customer(ST_SALESORDER, $order_no, $date_, '11120', 0, 0,
    //        $order->uang_muka, $order->customer_id,
    //        "The total debtor GL posting could not be inserted", 0, "Uang Muka");
    //
    //    add_comments($order->trans_type, $order_no, $date_, 'UANG MUKA');
    /* inserted line items into sales order details */
    add_audit_trail($order->trans_type, $order_no, $order->document_date);
    $Refs->save($order->trans_type, $order_no, $order->reference);

    hook_db_postwrite($order, $order->trans_type);
    commit_transaction();

    if ($loc_notification == 1 && count($st_ids) > 0)
    {
        require_once ($path_to_root . "/reporting/includes/class.mail.inc");
        $company = get_company_prefs();
        $mail = new email($company['coy_name'], $company['email']);
        $from = $company['coy_name'] . " <" . $company['email'] . ">";
        $to = $loc['location_name'] . " <" . $loc['email'] . ">";
        $subject = _("Stocks below Re-Order Level at " . $loc['location_name']);
        $msg = "\n";
        for ($i = 0; $i < count($st_ids); $i++)
            $msg .= $st_ids[$i] . " " . $st_names[$i] . ", " . _("Re-Order Level") . ": " .
                $st_reorder[$i] . ", " . _("Below") . ": " . $st_num[$i] . "\n";
        $msg .= "\n" . _("Please reorder") . "\n\n";
        $msg .= $company['coy_name'];
        $mail->to($to);
        $mail->subject($subject);
        $mail->text($msg);
        $ret = $mail->send();
    }


    return $order_no;
}

function add_pelunasan($order)
{
    //TODO: insert pelunasan
    global $loc_notification, $path_to_root, $Refs;

    begin_transaction();

    $order_no = $order->order_no;
    $date_ = $order->document_date;
    $sql = "select * from uang_muka where order_no=" . db_escape($order_no) . " ";
    $rs = db_query($sql);
    $row = db_fetch($rs);
    $total = $row['total'] - $row['pay1'];

    $sql = "update uang_muka set pay2=" . db_escape($total) . ",
	date_lunas='" . $date_ . "',
	isLunas=1
	where order_no=" . db_escape($order_no) . "";

    db_query($sql, "Pelunasan gagal dilakukan");

    add_bank_trans(ST_SALESORDER, $order_no, '1', $order->reference, $order->
        document_date, $total, PT_CUSTOMER, $order->Branch);

    $sql2 = "update sales_orders set status=1
	where order_no=" . db_escape($order_no) . "";

    db_query($sql2, "Pelunasan gagal dilakukan");


    //masuk gl


    //	$total = 0;
    //$customer = get_customer($order->customer_id);
    //Post sales transaction to GL credit sales

    $sales_account = '41300';
    $dim = 0;
    $dim2 = 0;
    //	$total +=
    add_gl_trans_customer(ST_SALESORDER, $order_no, $date_, $sales_account, $dim, $dim2,
        -$total, $order->customer_id,
        "Akun GL Kredit Faktur Penjualan Pesan tidak bisa diproses", 0, "Uang Pelunasan");

    //	$total +=
    add_gl_trans_customer(ST_SALESORDER, $order_no, $date_, '11120', 0, 0, $total, $order->
        customer_id, "Akun GL Debit Faktur Penjualan Pesan tidak bisa diproses", 0,
        "Uang Pelunasan");

    //add_comments($order->trans_type, $order_no, $date_, 'Pelunasan');

    update_comments(ST_SALESORDER, $order_no, $date_, 'LUNAS');

    commit_transaction();

}
//----------------------------------------------------------------------------------------

function get_sales_order_belum_lunas()
{
    $sql = "SELECT cust_branch.br_name, sales_orders.reference, sales_orders.total, total_bayar(sales_orders.order_no) as bayar,
    sales_orders.total - total_bayar(sales_orders.order_no) as saldo,sales_orders.ord_date,
    DATEDIFF(sales_orders.delivery_date,CURDATE()) AS dueday,sales_orders.order_no FROM sales_orders
    INNER JOIN cust_branch ON cust_branch.branch_code = sales_orders.branch_code
    WHERE sales_orders.total != 0 and sales_orders.total > total_bayar(sales_orders.order_no) and sales_orders.void != 1
    ORDER BY br_name";
    return db_query($sql, "error databases");
}

function delete_sales_order($order_no, $trans_type)
{
    begin_transaction();
    hook_db_prevoid($trans_type, $order_no);

    $sql = "DELETE FROM " . TB_PREF . "sales_orders WHERE order_no=" . db_escape($order_no) .
        " AND trans_type=" . db_escape($trans_type);

    db_query($sql, "order Header Delete");

    $sql = "DELETE FROM " . TB_PREF . "sales_order_details WHERE order_no =" .
        db_escape($order_no) . " AND trans_type=" . db_escape($trans_type);
    db_query($sql, "order Detail Delete");

    delete_reference($trans_type, $order_no);

    add_audit_trail($trans_type, $order_no, Today(), _("Deleted."));
    commit_transaction();
}

//----------------------------------------------------------------------------------------
// Mark changes in sales_order_details
//
function update_sales_order_version($order)
{
    foreach ($order as $so_num => $so_ver)
    {
        $sql = 'UPDATE ' . TB_PREF .
            'sales_orders SET version=version+1 WHERE order_no=' . db_escape($so_num) .
            ' AND version=' . $so_ver . " AND trans_type=30";
        db_query($sql, 'Concurrent editing conflict while sales order update');
    }
}

//----------------------------------------------------------------------------------------

function update_sales_order($order)
{
    global $loc_notification, $path_to_root, $Refs;

    $del_date = date2sql($order->due_date);
    $ord_date = date2sql($order->document_date);
    $order_no = key($order->trans_no);
    $version = current($order->trans_no);
    $total = $order->get_trans_total();

    begin_transaction();

    $sql = "UPDATE " . TB_PREF . "sales_orders SET type =" . db_escape($order->
        so_type) . " ,
	debtor_no = " . db_escape($order->customer_id) . ",
	branch_code = " . db_escape($order->Branch) . ",
	customer_ref = " . db_escape($order->cust_ref) . ",
	reference = " . db_escape($order->reference) . ",
	comments = " . db_escape($order->Comments) . ",
	ord_date = " . db_escape($ord_date) . ",
	order_type = " . db_escape($order->sales_type) . ",
	ship_via = " . db_escape($order->ship_via) . ",
	deliver_to = " . db_escape($order->deliver_to) . ",
	delivery_address = " . db_escape($order->delivery_address) . ",
	contact_phone = " . db_escape($order->phone) . ",
	freight_cost = " . db_escape($order->freight_cost) . ",
	from_stk_loc = " . db_escape($order->Location) . ",
	delivery_date = " . db_escape($del_date) . ",
	version = " . ($version + 1) . ",
	payment_terms = " . db_escape($order->payment) . ",
	total = " . db_escape($total) . "
	WHERE order_no=" . db_escape($order_no) . "
	AND trans_type=" . $order->trans_type . " AND version=" . $version;
    db_query($sql, "order Cannot be Updated, this can be concurrent edition conflict");

    $sql = "DELETE FROM " . TB_PREF . "sales_order_details WHERE order_no =" .
        db_escape($order_no) . " AND trans_type=" . $order->trans_type;

    db_query($sql, "Old order Cannot be Deleted");

    if ($loc_notification == 1)
    {
        include_once ($path_to_root . "/inventory/includes/inventory_db.inc");
        $st_ids = array();
        $st_names = array();
        $st_num = array();
        $st_reorder = array();
    }
    foreach ($order->line_items as $key => $line)
    {
        if ($loc_notification == 1 && is_inventory_item($line->stock_id))
        {
            $sql = "SELECT " . TB_PREF . "loc_stock.*, " . TB_PREF .
                "locations.location_name, " . TB_PREF . "locations.email
			FROM " . TB_PREF . "loc_stock, " . TB_PREF . "locations
			WHERE " . TB_PREF . "loc_stock.loc_code=" . TB_PREF . "locations.loc_code
			AND " . TB_PREF . "loc_stock.stock_id = " . db_escape($line->stock_id) . "
			AND " . TB_PREF . "loc_stock.loc_code = " . db_escape($order->Location);
            $res = db_query($sql, "a location could not be retreived");
            $loc = db_fetch($res);
            if ($loc['email'] != "")
            {
                $qoh = get_qoh_on_date($line->stock_id, $order->Location);
                $qoh -= get_demand_qty($line->stock_id, $order->Location);
                $qoh -= get_demand_asm_qty($line->stock_id, $order->Location);
                $qoh -= $line->quantity;
                if ($qoh < $loc['reorder_level'])
                {
                    $st_ids[] = $line->stock_id;
                    $st_names[] = $line->item_description;
                    $st_num[] = $qoh - $loc['reorder_level'];
                    $st_reorder[] = $loc['reorder_level'];
                }
            }
        }
        $sql = "INSERT INTO " . TB_PREF . "sales_order_details
		(id, order_no, trans_type, stk_code,  description, unit_price, quantity,
		discount_percent, qty_sent,ref)
		VALUES (";

        $myref = $order->reference;
        if ($myref != "auto")
        {
            $myref = str_replace("FPP", "NP", $myref);
            $myref .= $key;
        }

        $sql .= db_escape($line->id ? $line->id : 0) . "," . $order_no . "," . $order->
            trans_type . "," . db_escape($line->stock_id) . "," . db_escape($line->
            item_description) . ", " . db_escape($line->price) . ", " . db_escape($line->
            quantity) . ", " . db_escape($line->discount_percent) . ", " . db_escape($line->
            qty_done) . "," . db_escape($myref) . " )";

        db_query($sql, "Old order Cannot be Inserted");

        $row = array(
            'jenis_barang' => $line->jenis_barang,
            'color' => $line->color,
            'kombinasi' => $line->kombinasi,
            'size_ss' => $line->size_ss,
            'price_ss' => $line->price_ss,
            'size_s' => $line->size_s,
            'size_m' => $line->size_m,
            'size_l' => $line->size_l,
            'size_ml' => $line->size_ml,
            'size_xxl' => $line->size_xxl,
            'size_xxxl' => $line->size_xxxl,
            'size_3l' => $line->size_3l,
            'size_other' => $line->size_other,
            'price_s' => $line->price_s,
            'price_m' => $line->price_m,
            'price_l' => $line->price_l,
            'price_ml' => $line->price_ml,
            'price_xxl' => $line->price_xxl,
            'price_xxxl' => $line->price_xxxl,
            'price_3l' => $line->price_3l,
            'price_other' => $line->price_other,
            'sablon' => $line->sablon,
            'model_jahit' => $line->model_jahit,
            'memo' => $line->memo,
            );

        save_sales_order_item_details($myref, $row);

    }
    /* inserted line items into sales order details */

    add_audit_trail($order->trans_type, $order_no, $order->document_date, _("Updated."));
    $Refs->save($order->trans_type, $order_no, $order->reference);
    commit_transaction();
    if ($loc_notification == 1 && count($st_ids) > 0)
    {
        require_once ($path_to_root . "/reporting/includes/class.mail.inc");
        $company = get_company_prefs();
        $mail = new email($company['coy_name'], $company['email']);
        $from = $company['coy_name'] . " <" . $company['email'] . ">";
        $to = $loc['location_name'] . " <" . $loc['email'] . ">";
        $subject = _("Stocks below Re-Order Level at " . $loc['location_name']);
        $msg = "\n";
        for ($i = 0; $i < count($st_ids); $i++)
            $msg .= $st_ids[$i] . " " . $st_names[$i] . ", " . _("Re-Order Level") . ": " .
                $st_reorder[$i] . ", " . _("Below") . ": " . $st_num[$i] . "\n";
        $msg .= "\n" . _("Please reorder") . "\n\n";
        $msg .= $company['coy_name'];
        $mail->to($to);
        $mail->subject($subject);
        $mail->text($msg);
        $ret = $mail->send();
    }
}

//----------------------------------------------------------------------------------------

function get_sales_order_header($order_no, $trans_type)
{
    $sql = "SELECT sorder.*, " . "cust.name, " . "cust.address, " .
        "cust.curr_code, " . "loc.location_name, " . "cust.discount, " .
        "stype.sales_type, " . "stype.id AS sales_type_id, " . "stype.tax_included, " .
        "ship.shipper_name, " . "tax_group.name AS tax_group_name , " .
        "tax_group.id AS tax_group_id, " . "cust.tax_id " . "FROM " . TB_PREF .
        "sales_orders sorder, " . TB_PREF . "debtors_master cust," . TB_PREF .
        "sales_types stype, " . TB_PREF . "tax_groups tax_group, " . TB_PREF .
        "cust_branch branch," . TB_PREF . "locations loc, " . TB_PREF . "shippers ship
	WHERE sorder.order_type=stype.id
	AND branch.branch_code = sorder.branch_code
	AND branch.tax_group_id = tax_group.id
	AND sorder.debtor_no = cust.debtor_no
	AND loc.loc_code = sorder.from_stk_loc
	AND ship.shipper_id = sorder.ship_via
	AND sorder.trans_type = " . db_escape($trans_type) . "
	AND sorder.order_no = " . db_escape($order_no);

    $result = db_query($sql, "order Retreival");

    $num = db_num_rows($result);
    if ($num > 1)
    {
        display_warning("You have duplicate document in database: (type:$trans_type, number:$order_no).");
    } else
        if ($num == 1)
        {
            return db_fetch($result);
        } else
            display_warning("You have missing or invalid sales document in database (type:$trans_type, number:$order_no).");

}

function get_cust_pay($order_no)
{
    $sql = "Select * from customers_trans
    where not void and so_id = " . db_escape($order_no);
    return db_query($sql);
}

function get_qty_sales_order_item_details($ref)
{
    //by ref
    $sql = "SELECT (size_ss + size_s + size_m + size_l + size_ml + size_xxl + size_xxxl + size_3l + size_other) AS jml
    FROM sales_order_item_details WHERE id = " . db_escape($ref);
    return db_result(db_query($sql));
}

function get_total_pay($order)
{
    $sql = "select sum(amount) as total from customers_trans where not void and  
    so_id = " . db_escape($order);
    $result = db_query($sql);
    $total = db_fetch($result);
    return $total['total'];
}

function is_first_pay($order_no)
{
    $sql = "select amount from customers_trans where so_id = " . db_escape($order_no);
    $result = db_query($sql);
    return db_num_rows($result) == 0;
}

function void_cust_pay($id)
{
    global $Refs;
    begin_transaction();     
    $sql_total = "select total,acc_rek,debtor_no from sales_orders where order_no in (SELECT so_id from customers_trans where id = " . db_escape($id) . ")";
    $result_tot = db_query($sql_total);
    $res = db_fetch($result_tot);
    $acc_rek = $res['acc_rek'];
    $debtor_no = $res['debtor_no'];
    
    $sql = "update customers_trans set void = 1 where id = " . db_escape($id);
    db_query($sql, "Void pembayaran gagal dilakukan");
    $getinfo = "select so_id,ref,tipe,date_,amount from customers_trans where id = ".db_escape($id);
    $rslinfo = db_query($getinfo);
    $info = db_fetch($rslinfo);
    
    $sqlvoidbank = "select * from bank_trans where type = ".db_escape(ST_SALESORDER).
    " AND trans_no = ".db_escape($info['so_id'])." AND ref = ".db_escape($info['ref']);
    $rslbank = db_query($sqlvoidbank, "pengambilan transaksi bank gagal dilakukan");
    $info_bank = db_fetch($rslbank);
    
    //$sqlvoidgl = "delete from gl_trans where type = ".db_escape(ST_SALESORDER).
//    " AND type_no = ".db_escape($info['so_id'])." AND memo_ = ".db_escape($info['tipe']);
//    db_query($sqlvoidgl, "Void transaksi gl gagal dilakukan");
    
    $account_bank = get_account_code_bank($info_bank['bank_act']);
    //--------------------- void all payment
    $trans_type = 0;
    $trans_id = get_next_trans_no($trans_type);	
	$ref = $Refs->get_next(0);
    $date_ = sql2date($info['date_']);
    $amount = $info['amount'];
    $myrow = get_company_prefs();
        //jurnal pembalik
        if($debtor_no == 3){
            add_gl_trans($trans_type, $trans_id, $date_, $acc_rek,0, 0,"", $amount);
        }
        else {
        add_gl_trans($trans_type, $trans_id, $date_, $myrow["default_sales_act"],0, 0,"", $amount);
        }
        //void bank trans
		add_gl_trans($trans_type, $trans_id, $date_, $account_bank,0, 0,"", -($amount));	
		add_bank_trans($trans_type, $trans_id, $info_bank['bank_act'], $ref,
				$date_, -($amount),	0, "", get_company_currency(),
				"Cannot insert a destination bank transaction");	
	
	$Refs->save($trans_type, $trans_id, $ref);
    add_comments($trans_type, $trans_id, $date_, 'VOID TRANSAKSI PELANGGAN : '.$info['ref']);
	add_audit_trail($trans_type, $trans_id, $date_);
    
    //---------------------
    
    commit_transaction();
}

function save_cust_pay($ref, $kwitansi, $order_no, $date_, $amount, $customer_id,
    $bank_id, $note = '')
{
    global $Refs;

    begin_transaction();
    $order_no_next = get_next_autoincrement('customers_trans');
    $sql_total = "select total,acc_rek,debtor_no from sales_orders where order_no = " . db_escape($order_no);
    $result_tot = db_query($sql_total);
    $res = db_fetch($result_tot);

    $total = $res['total'];
    $acc_rek = $res['acc_rek'];
    $debtor_no = $res['debtor_no'];
    $total_current_pay = get_total_pay($order_no);

    $sql_ = "select * from customers_trans where so_id = " . db_escape($order_no);
    $result_ = db_query($sql_);
    $counter = db_num_rows($result_);
    $tipe = '';
    $counter++;

    if (($total - $total_current_pay - $amount) == 0)
        $tipe = "LUNAS";
    else
        $tipe = "UANG MUKA " . $counter;

    if (($total - $total_current_pay - $amount) == 0)
        close_sales_order($order_no);

    $sql = "INSERT INTO customers_trans(ref,so_id,date_,note,amount,tipe,no_kwitansi) values(
    " . db_escape($ref) . "," . db_escape($order_no) . "," . db_escape(date2sql
        ($date_)) . "," . db_escape($note) . "," . db_escape($amount) . "," . db_escape($tipe) .
        "," . db_escape($kwitansi) . ")";
    db_query($sql);

    // TODO: date_ -> musti di benerin
    //bank  transaksi - debet
    $sales_account = get_account_code_bank($bank_id); //$stock_gl_code['sales_account'];
    $dim = 0;
    $dim2 = 0;
    $total += add_gl_trans_customer(ST_SALESORDER, $order_no, $date_, $sales_account,
        $dim, $dim2, $amount, $customer_id,
        "Akun GL Faktur Penjualan Pesan tidak bisa di - insert", 0, $tipe);
        
    add_bank_trans(ST_SALESORDER, $order_no, $bank_id, $ref, $date_, $amount,
        PT_CUSTOMER, $customer_id);
    //}
    $myrow = get_company_prefs();

    //default akun penjualan - Kredit    ato akun piutang untuk penjualan khusus       
    if($debtor_no == 3){
        $total += add_gl_trans_customer(ST_SALESORDER, $order_no, $date_, $acc_rek,
        0, 0, -$amount, $customer_id,
        "Akun GL Faktur Penjualan Pesan tidak bisa di - insert", 0, $tipe);
    }   else
    {
    $total += add_gl_trans_customer(ST_SALESORDER, $order_no, $date_, $myrow["default_sales_act"],
        0, 0, -$amount, $customer_id,
        "Akun GL Faktur Penjualan Pesan tidak bisa di - insert", 0, $tipe);
    }
    

    add_comments(ST_SALESORDER, $order_no, $date_, $tipe);

    $Refs->save(ST_PEMBAYARAN, $order_no_next, $ref);

    commit_transaction();
}

function get_nota_pesan_header($order_no, $trans_type)
{
    $sql = "SELECT sorder.*,np.id as id_np, " . "cust.name, " . "cust.address, " .
        "cust.curr_code, " . "loc.location_name, " . "cust.discount, " .
        "stype.sales_type, " . "stype.id AS sales_type_id, " //."stype.tax_inxcluded, "
        . "ship.shipper_name, " . "tax_group.name AS tax_group_name , " .
        "tax_group.id AS tax_group_id, " . "cust.tax_id " . "FROM " . TB_PREF .
        "sales_orders sorder, " . TB_PREF . "debtors_master cust," . TB_PREF .
        "sales_types stype, " . TB_PREF . "tax_groups tax_group, " . TB_PREF .
        "cust_branch branch," . TB_PREF . "locations loc, " . TB_PREF . "shippers ship," .
        TB_PREF . "sales_order_details dt," . TB_PREF . "sales_order_item_details np
	WHERE sorder.order_type=stype.id
	AND branch.branch_code = sorder.branch_code
	AND branch.tax_group_id = tax_group.id
	AND sorder.debtor_no = cust.debtor_no
	AND loc.loc_code = sorder.from_stk_loc
	AND ship.shipper_id = sorder.ship_via
	and sorder.order_no = dt.order_no
	and dt.ref=np.id
	AND sorder.trans_type = " . db_escape($trans_type) . "
	AND np.id = " . db_escape($order_no);

    $result = db_query($sql, "order Retreival");

    $num = db_num_rows($result);
    if ($num > 1)
    {
        display_warning("You have duplicate document in database: (type:$trans_type, number:$order_no).");
    } else
        if ($num == 1)
        {
            return db_fetch($result);
        } else
            display_warning("You have missing or invalid sales document in database (type:$trans_type, number:$order_no).");

}
function get_fakturtunaiheader($order_no, $trans_type)
{
    $sql = "SELECT so.order_no, " . "hd.reference,hd.tran_date, " . "dm.* " .
        "FROM " . TB_PREF . "debtor_trans hd, " . TB_PREF . "debtors_master dm, " .
        TB_PREF . "sales_orders so
	WHERE so.order_no=hd.order_
	AND hd.debtor_no = dm.debtor_no
	AND hd.type = " . db_escape($trans_type) . "
	AND so.order_no = " . db_escape($order_no);

    $result = db_query($sql, "order Retreival");

    $num = db_num_rows($result);
    if ($num > 1)
    {
        display_warning("You have duplicate document in database: (type:$trans_type, number:$order_no).");
    } else
        if ($num == 1)
        {
            return db_fetch($result);
        } else
            display_warning("You have missing or invalid sales document in database (type:$trans_type, number:$order_no).");

}


function get_fakturtunaidetail($order_no, $trans_type)
{
    $sql = "SELECT so.order_no, " . "hd.*, " . "dt.*, " . "dm.*, " . "sm.units " .
        "FROM " . TB_PREF . "debtor_trans hd, " . TB_PREF . "debtor_trans_details dt, " .
        TB_PREF . "debtors_master dm, " . TB_PREF . "sales_orders so," . TB_PREF .
        "stock_master sm
	WHERE hd.trans_no=dt.debtor_trans_no
	AND hd.debtor_no = dm.debtor_no
	AND so.order_no=hd.order_
	AND dt.stock_id=sm.stock_id
	AND hd.type = " . db_escape($trans_type) . "
	and dt.debtor_trans_type = " . db_escape($trans_type) . "
	AND so.order_no = " . db_escape($order_no);

    return db_query($sql, "Retreive order Line Items");
}

//----------------------------------------------------------------------------------------

function get_sales_order_details_view($ref)
{
    $sql = "SELECT *
	FROM
	sales_order_details
	INNER JOIN sales_orders
	ON sales_order_details.order_no = sales_orders.order_no
	WHERE ref =" . db_escape($ref) . " ORDER BY id";

    return db_query($sql, "Retreive order Line Items");
}

function get_sales_order_details($order_no, $trans_type)
{
    $sql = "SELECT id, stk_code, unit_price, " . TB_PREF .
        "sales_order_details.ref as ref," . TB_PREF . "sales_order_details.description," .
        TB_PREF . "sales_order_details.quantity,
	discount_percent,
	qty_sent as qty_done, " . TB_PREF . "stock_master.units,
	" . TB_PREF . "stock_master.material_cost + " . TB_PREF .
        "stock_master.labour_cost + " . TB_PREF .
        "stock_master.overhead_cost AS standard_cost
	FROM " . TB_PREF . "sales_order_details, " . TB_PREF . "stock_master
	WHERE " . TB_PREF . "sales_order_details.stk_code = " . TB_PREF .
        "stock_master.stock_id
	AND order_no =" . db_escape($order_no) . " AND trans_type = " . db_escape($trans_type) .
        " ORDER BY id";

    return db_query($sql, "Retreive order Line Items");
}
//----------------------------------------------------------------------------------------

function read_sales_order($order_no, &$order, $trans_type)
{

    $myrow = get_sales_order_header($order_no, $trans_type);

    $order->trans_type = $myrow['trans_type'];
    $order->so_type = $myrow["type"];
    $order->trans_no = array($order_no => $myrow["version"]);

    $order->set_customer($myrow["debtor_no"], $myrow["name"], $myrow["curr_code"], $myrow["discount"],
        $myrow["payment_terms"]);

    $order->set_branch($myrow["branch_code"], $myrow["tax_group_id"], $myrow["tax_group_name"],
        $myrow["contact_phone"]);

    $order->set_sales_type($myrow["sales_type_id"], $myrow["sales_type"], $myrow["tax_included"],
        0); // no default price calculations on edit

    $order->set_location($myrow["from_stk_loc"], $myrow["location_name"]);

    $order->set_delivery($myrow["ship_via"], $myrow["deliver_to"], $myrow["delivery_address"],
        $myrow["freight_cost"]);

    $order->cust_ref = $myrow["customer_ref"];
    $order->sales_type = $myrow["order_type"];
    $order->reference = $myrow["reference"];
    $order->Comments = $myrow["comments"];
    $order->due_date = sql2date($myrow["delivery_date"]);
    $order->document_date = sql2date($myrow["ord_date"]);

    $um = get_uang_muka($order_no);
    $order->uang_muka = $um['pay1'];
    $order->ref_so = $myrow["reference"];

    $result = get_sales_order_details($order_no, $order->trans_type);

    if (db_num_rows($result) > 0)
    {
        $line_no = 0;
        while ($myrow = db_fetch($result))
        {

            $order->add_to_cart($line_no, $myrow["stk_code"], $myrow["quantity"], $myrow["unit_price"],
                $myrow["discount_percent"], $myrow["qty_done"], $myrow["standard_cost"], $myrow["description"],
                $myrow["id"], 0, 0, get_sales_order_item_details($myrow["ref"]));

            $line_no++;
            //$item_details = get_sales_order_item_details($myrow["ref"]);
            //            display_error($item_details['size_s']);
            //display_error($myrow["ref"]);
        }
    }

    return true;
}

//----------------------------------------------------------------------------------------

function sales_order_has_deliveries($order_no)
{
    $sql = "SELECT SUM(qty_sent) FROM " . TB_PREF .
        "sales_order_details WHERE order_no=" . db_escape($order_no) .
        " AND trans_type=" . ST_SALESORDER;

    $result = db_query($sql, "could not query for sales order usage");

    $row = db_fetch_row($result);

    if ($row[0] > 0)
        return true; // 2010-04-21 added check for eventually voided deliveries, Joe Hunt
    $sql = "SELECT order_ FROM " . TB_PREF . "debtor_trans WHERE type=" .
        ST_CUSTDELIVERY . " AND order_=" . db_escape($order_no);
    $result = db_query($sql, "The related delivery notes could not be retreived");
    return (db_num_rows($result) > 0);
}

//----------------------------------------------------------------------------------------

function get_sales_order_item_details($ref)
{
    $sql = "SELECT *,sales_order_item_details.jns_brg as jenis_barang
     FROM " . TB_PREF . "sales_order_item_details
	WHERE id = " . db_escape($ref);

    $result = db_query($sql, "order Retreival");

    $num = db_num_rows($result);
    if ($num > 1)
    {
        display_warning("You have duplicate document in database: (Reference:$ref).");
    } else
        if ($num == 1)
        {
            return db_fetch($result);
        } else
            return null;
}

//----------------------------------------------------------------------------------------

function save_sales_order_item_details($ref, $row)
{
    $sql = "Replace " . TB_PREF . "sales_order_item_details SET
	jns_brg=" . db_escape($row['jenis_barang']) . ",color=" . db_escape($row['color']) .
        ",kombinasi=" . db_escape($row['kombinasi']) . ",size_ss=" . db_escape($row['size_ss']) .
        ",price_ss=" . db_escape($row['price_ss']) . ",size_s=" . db_escape($row['size_s']) .
        ",price_s=" . db_escape($row['price_s']) . ",size_m=" . db_escape($row['size_m']) .
        ",price_m=" . db_escape($row['price_m']) . ",size_l=" . db_escape($row['size_l']) .
        ",price_l=" . db_escape($row['price_l']) . ",size_ml=" . db_escape($row['size_ml']) .
        ",price_ml=" . db_escape($row['price_ml']) . ",size_xxl=" . db_escape($row['size_xxl']) .
        ",price_xxl=" . db_escape($row['price_xxl']) . ",size_xxxl=" . db_escape($row['size_xxxl']) .
        ",price_xxxl=" . db_escape($row['price_xxxl']) . ",size_3l=" . db_escape($row['size_3l']) .
        ",price_3l=" . db_escape($row['price_3l']) . ",size_other=" . db_escape($row['size_other']) .
        ",price_other=" . db_escape($row['price_other']) . ",sablon=" . db_escape($row['sablon']) .
        ",model_jahit=" . db_escape($row['model_jahit']) . ",memo=" . db_escape($row['memo']) .
        ",id=" . db_escape($ref);

    db_query($sql, "Detil item can't save");
}

//----------------------------------------------------------------------------------------

function close_sales_order($order_no)
{
    begin_transaction();
    // set the quantity of each item to the already sent quantity. this will mark item as closed.
    $sql = "UPDATE " . TB_PREF . "sales_order_details
	SET qty_sent =quantity,isClose = 1 WHERE order_no = " . db_escape($order_no) .
        " AND trans_type=" . ST_SALESORDER;

    db_query($sql, "The sales order detail record could not be updated");

    //$result = db_query("select reference from sales_orders where order_no =" .
//        db_escape($order_no));
//    $row = db_fetch($result);

    //	if($row['reference']!='auto')
    //	{
    $sql = "UPDATE " . TB_PREF . "sales_orders set status = 1 WHERE order_no = " .
        db_escape($order_no) . " AND trans_type=" . ST_SALESORDER;
    db_query($sql);

     /*$sql_detil = "UPDATE sales_order_details set isClose = 1 WHERE order_no = " .
        db_escape($order_no) . " AND trans_type=" . ST_SALESORDER;
        
    db_query($sql_detil);    */
    /**
     *  $sql = "update " . TB_PREF . "uang_muka set isLunas = 1, date_lunas = " .
     *         db_escape(date2sql(Today())) . ", pay2 = (SELECT total from " . TB_PREF .
     *         "sales_orders where order_no = " . db_escape($order_no) .
     *         ") - pay1 where order_no = " . db_escape($order_no);
     *     db_query($sql);

     *     $sql = "UPDATE " . TB_PREF . "sales_orders set status = 1 WHERE
     * 	(reference = 'auto' or reference like 'INP%') AND trans_type=" . ST_SALESORDER;
     *     db_query($sql);
     */
    //}
    commit_transaction();
}

//---------------------------------------------------------------------------------------------------------------

function void_sales_orders($row)
{
    //begin_transaction();
    global $Refs;
    $sql = "update sales_orders set void = 1 where order_no = ".db_escape($row);
    db_query($sql);
    $sql_total = "select total,acc_rek,debtor_no,ord_date,reference from sales_orders where order_no = " . db_escape($row);
    $result_tot = db_query($sql_total);
    $res = db_fetch($result_tot);
    $total = $res['total'];
    $acc_rek = $res['acc_rek'];
    $debtor_no = $res['debtor_no'];
    
    //jurnal pembalik utang
    //add_gl_trans($trans_type, $trans_id, $date_, $myrow["default_sales_act"],0, 0,"", $amount);
    
    
    $cust_trans = get_cust_pay($row);
    while($cust_tran = db_fetch($cust_trans))
    {
        void_cust_pay($cust_tran['id']);
    }   
    
    $trans_type = 0;
    $trans_id = get_next_trans_no($trans_type);    
    $ref = $Refs->get_next(0);
    $date_ = sql2date($res['ord_date']);
    //$amount = $info['amount'];
    //debit
    if($debtor_no == 3){
        $myrow = get_company_prefs();
        //debit
        add_gl_trans($trans_type, $row, $date_, $myrow["default_sales_act"],0, 0,"", $total);
        //kredit
        add_gl_trans($trans_type, $row, $date_, $acc_rek,0, 0,"", -$total);
        $Refs->save($trans_type, $trans_id, $ref);
        add_comments($trans_type, $trans_id, $date_, 'VOID TRANSAKSI PELANGGAN KHUSUS: '.$res['reference']);
        add_audit_trail($trans_type, $trans_id, $date_);
    }
    //commit_transaction();
}

function is_faktur_lunas($order_no)
{
    $sql = "SELECT * FROM
			" . TB_PREF . "sales_orders so
			WHERE so.order_no = " . db_escape($order_no) . "
			AND so.status";
    $result = db_query($sql);
    $num = db_num_rows($result);
    return $num > 0;
}

function is_order_finish_all($order_no)
{
    $sql = "SELECT sales_order_details.ref
    FROM " . TB_PREF . "sales_order_details
    LEFT OUTER JOIN sales_orders
    ON sales_order_details.order_no = sales_orders.order_no
    WHERE sales_orders.order_no = " . db_escape($order_no) . "
    AND sales_order_details.isClose = 0";
    $result = db_query($sql);
    $num = db_num_rows($result);
    return $num == 0;
}

function get_invoice_duedate($terms, $invdate)
{
    if (!is_date($invdate))
    {
        return new_doc_date();
    }

    $myrow = get_payment_terms($terms);

    if (!$myrow)
        return $invdate;

    if ($myrow['day_in_following_month'] > 0)
        $duedate = add_days(end_month($invdate), $myrow['day_in_following_month']);
    else
        $duedate = add_days($invdate, $myrow['days_before_due']);
    return $duedate;
}

function get_customer_to_order($customer_id)
{

    // Now check to ensure this account is not on hold */
    $sql = "SELECT cust.name,
			cust.address, " . TB_PREF . "credit_status.dissallow_invoices,
			cust.sales_type AS salestype,
			cust.dimension_id,
			cust.dimension2_id,
			stype.sales_type,
			stype.tax_included,
			stype.factor,
			cust.curr_code,
			cust.discount,
			cust.payment_terms,
			cust.pymt_discount,
			cust.credit_limit - Sum(IFNULL(IF(trans.type=11 OR trans.type=12 OR trans.type=2,
			-1, 1) * (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount),0)) as cur_credit
			FROM " . TB_PREF . "debtors_master cust
			LEFT JOIN " . TB_PREF . "debtor_trans trans ON trans.type!=" .
        ST_CUSTDELIVERY . " AND trans.debtor_no = cust.debtor_no," . TB_PREF .
        "credit_status, " . TB_PREF . "sales_types stype
			WHERE cust.sales_type=stype.id
			AND cust.credit_status=" . TB_PREF . "credit_status.id
			AND cust.debtor_no = " . db_escape($customer_id) . " GROUP by cust.debtor_no";

    $result = db_query($sql, "Customer Record Retreive");
    return db_fetch($result);
}

function get_branch_to_order($customer_id, $branch_id)
{

    // the branch was also selected from the customer selection so default the delivery details from the customer branches table cust_branch. The order process will ask for branch details later anyway
    $sql = "SELECT " . TB_PREF . "cust_branch.br_name, " . TB_PREF .
        "cust_branch.br_address, " . TB_PREF . "cust_branch.br_post_address, " .
        " default_location, location_name, default_ship_via, " . TB_PREF .
        "tax_groups.name AS tax_group_name, " . TB_PREF . "tax_groups.id AS tax_group_id
			FROM " . TB_PREF . "cust_branch, " . TB_PREF . "tax_groups, " . TB_PREF .
        "locations
			WHERE " . TB_PREF . "cust_branch.tax_group_id = " . TB_PREF . "tax_groups.id
			AND " . TB_PREF . "locations.loc_code=default_location
			AND " . TB_PREF . "cust_branch.branch_code=" . db_escape($branch_id) . "
			AND " . TB_PREF . "cust_branch.debtor_no = " . db_escape($customer_id);

    return db_query($sql, "Customer Branch Record Retreive");
}

function get_sql_for_sales_orders_view($selected_customer, $trans_type, $trans_no,
    $filter, $stock_item = null, $from = '', $to = '', $ref = '', $location = '', $customer_id =
    ALL_TEXT)
{

    $sql = "SELECT
			sorder.order_no,
			sorder.reference,
			debtor.name,
			branch.br_name," . ($filter == 'InvoiceTemplates' || $filter ==
        'DeliveryTemplates' ? "sorder.comments, " : "sorder.customer_ref, ") .
        "sorder.ord_date,
					sorder.delivery_date,
					sorder.deliver_to,
					Sum((line.unit_price*line.quantity)-line.discount_percent)+freight_cost AS OrderValue,
					sorder.type,
					debtor.curr_code,
					Sum(line.qty_sent) AS TotDelivered,
					Sum(line.quantity) AS TotQuantity
					FROM " . TB_PREF . "sales_orders as sorder, " . TB_PREF .
        "sales_order_details as line, " . TB_PREF . "debtors_master as debtor, " .
        TB_PREF . "cust_branch as branch
					WHERE sorder.order_no = line.order_no
					AND sorder.reference <> 'auto'
					AND sorder.status = 0
					AND sorder.trans_type = line.trans_type
					AND sorder.trans_type = " . db_escape($trans_type) . "
					AND sorder.debtor_no = debtor.debtor_no
					AND sorder.branch_code = branch.branch_code
					AND debtor.debtor_no = branch.debtor_no";

    if (isset($trans_no) && $trans_no != "")
    {
        // search orders with number like
        $number_like = "%" . $trans_no;
        $sql .= " AND sorder.order_no LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } elseif ($ref != "")
    {
        // search orders with reference like
        $number_like = "%" . $ref . "%";
        $sql .= " AND sorder.reference LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } else // ... or select inquiry constraints
    {
        if ($filter != 'DeliveryTemplates' && $filter != 'InvoiceTemplates' && $filter !=
            'OutstandingOnly')
        {
            $date_after = date2sql($from);
            $date_before = date2sql($to);

            $sql .= " AND sorder.ord_date >= '$date_after'" . " AND sorder.ord_date <= '$date_before'";
        }
    }
    if ($trans_type == ST_SALESQUOTE && !check_value('show_all'))
        $sql .= " AND sorder.delivery_date >= '" . date2sql(Today()) .
            "' AND line.qty_sent=0"; // show only outstanding, not realized quotes

    if ($selected_customer != -1)
        $sql .= " AND sorder.debtor_no=" . db_escape($selected_customer);

    if (isset($stock_item))
        $sql .= " AND line.stk_code=" . db_escape($stock_item);

    if ($location)
        $sql .= " AND sorder.from_stk_loc = " . db_escape($location);

    if ($filter == 'OutstandingOnly')
        $sql .= " AND line.qty_sent < line.quantity";

    elseif ($filter == 'InvoiceTemplates' || $filter == 'DeliveryTemplates')
        $sql .= " AND sorder.type=1";

    //Chaiatanya : New Filter
    if ($customer_id != ALL_TEXT)
        $sql .= " AND sorder.debtor_no = " . db_escape($customer_id);

    $sql .= " GROUP BY sorder.order_no,
			sorder.debtor_no,
			sorder.branch_code,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.deliver_to";
    return $sql;
}

function get_sql_for_sales_orders_view2($selected_customer, $trans_type, $trans_no,
    $filter, $stock_item = null, $from = '', $to = '', $ref = '', $location = '', $customer_id =
    ALL_TEXT, $selected_trans_status = 0)
{

    $sql = "SELECT
			sorder.order_no,
			sorder.reference,
			debtor.name,
			branch.br_name," // 			.($filter=='InvoiceTemplates'
        // 			|| $filter=='DeliveryTemplates' ?
    // 			 "sorder.comments, " : "sorder.customer_ref, ")

    . "sorder.ord_date,

			sorder.delivery_date," . //sorder.deliver_to,
        //"Sum((line.unit_price*line.quantity)-line.discount_percent)+freight_cost AS OrderValue,
        "sorder.total,
			sorder.type," . //  			debtor.curr_code,
        "Sum(line.qty_sent) AS TotDelivered,
			Sum(line.quantity) AS TotQuantity
			FROM " . TB_PREF . "sales_orders as sorder, " . TB_PREF .
        "sales_order_details as line, " . TB_PREF . "debtors_master as debtor, " .
        TB_PREF . "cust_branch as branch
			WHERE sorder.void = 0 AND sorder.order_no = line.order_no
			AND sorder.status = " . db_escape($selected_trans_status) . "
			AND sorder.trans_type = line.trans_type
			AND sorder.trans_type = " . db_escape($trans_type) . "
			AND sorder.debtor_no = debtor.debtor_no
			AND sorder.branch_code = branch.branch_code
			AND debtor.debtor_no = branch.debtor_no";

    if (isset($trans_no) && $trans_no != "")
    {
        // search orders with number like
        $number_like = "%" . $trans_no;
        $sql .= " AND sorder.order_no LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } elseif ($ref != "")
    {
        // search orders with reference like
        $number_like = "%" . $ref . "%";
        $sql .= " AND sorder.reference LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } else // ... or select inquiry constraints
    {
        if ($filter != 'DeliveryTemplates' && $filter != 'InvoiceTemplates' && $filter !=
            'OutstandingOnly')
        {
            $date_after = date2sql($from);
            $date_before = date2sql($to);

            $sql .= " AND sorder.ord_date >= '$date_after'" . " AND sorder.ord_date <= '$date_before'";
        }
    }
    if ($trans_type == ST_SALESQUOTE && !check_value('show_all'))
        $sql .= " AND sorder.delivery_date >= '" . date2sql(Today()) .
            "' AND line.qty_sent=0"; // show only outstanding, not realized quotes

    if ($selected_customer != -1)
        $sql .= " AND sorder.debtor_no=" . db_escape($selected_customer);

    if (isset($stock_item))
        $sql .= " AND line.stk_code=" . db_escape($stock_item);

    if ($location)
        $sql .= " AND sorder.from_stk_loc = " . db_escape($location);

    if ($filter == 'OutstandingOnly')
        $sql .= " AND line.qty_sent < line.quantity";

    elseif ($filter == 'InvoiceTemplates' || $filter == 'DeliveryTemplates')
        $sql .= " AND sorder.type=1";

    //Chaiatanya : New Filter
    if ($customer_id != ALL_TEXT)
        $sql .= " AND sorder.debtor_no = " . db_escape($customer_id);

    $sql .= " GROUP BY sorder.order_no,
			sorder.debtor_no,
			sorder.branch_code,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.deliver_to";
    return $sql;
}

function get_sql_for_sales_orders_details_view($selected_customer, $trans_type,
    $trans_no, $filter, $stock_item = null, $from = '', $to = '', $ref = '', $location =
    '', $customer_id = ALL_TEXT, $selected_trans_status = 0)
{

    $sql = "SELECT
			line.id,
			line.ref,
			sorder.reference,
			debtor.name,
			branch.br_name,
			sorder.ord_date,
					sorder.delivery_date,
					Sum((line.unit_price*line.quantity)-line.discount_percent)+freight_cost AS OrderValue,
					sorder.type,
					debtor.curr_code,
					Sum(line.qty_sent) AS TotDelivered,
					Sum(line.quantity) AS TotQuantity,
					line.isclose
					FROM " . TB_PREF . "sales_orders as sorder, " . TB_PREF .
        "sales_order_details as line, " . TB_PREF . "debtors_master as debtor, " .
        TB_PREF . "cust_branch as branch
					WHERE sorder.order_no = line.order_no					
					AND sorder.trans_type = line.trans_type
					AND sorder.trans_type = " . db_escape($trans_type) . "
					AND sorder.debtor_no = debtor.debtor_no
					AND sorder.branch_code = branch.branch_code
					AND debtor.debtor_no = branch.debtor_no";


    if ($selected_trans_status == 1 || $selected_trans_status == 0)
    {
        $sql .= " AND sorder.status = " . db_escape($selected_trans_status);
    }

    if (isset($trans_no) && $trans_no != "")
    {
        // search orders with number like
        $number_like = "%" . $trans_no;
        $sql .= " AND sorder.order_no LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } elseif ($ref != "")
    {
        // search orders with reference like
        $number_like = "%" . $ref . "%";
        $sql .= " AND sorder.reference LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } else // ... or select inquiry constraints
    {
        if ($filter != 'DeliveryTemplates' && $filter != 'InvoiceTemplates' && $filter !=
            'OutstandingOnly')
        {
            $date_after = date2sql($from);
            $date_before = date2sql($to);

            $sql .= " AND sorder.ord_date >= '$date_after'" . " AND sorder.ord_date <= '$date_before'";
        }
    }


    if ($trans_type == ST_SALESQUOTE && !check_value('show_all'))
        $sql .= " AND sorder.delivery_date >= '" . date2sql(Today()) .
            "' AND line.qty_sent=0"; // show only outstanding, not realized quotes

    if ($selected_customer != -1)
        $sql .= " AND sorder.debtor_no=" . db_escape($selected_customer);

    if (isset($stock_item))
        $sql .= " AND line.stk_code=" . db_escape($stock_item);

    if ($location)
        $sql .= " AND sorder.from_stk_loc = " . db_escape($location);

    if ($filter == 'OutstandingOnly')
        $sql .= " AND line.qty_sent < line.quantity";

    elseif ($filter == 'InvoiceTemplates' || $filter == 'DeliveryTemplates')
        $sql .= " AND sorder.type=1";

    //Chaiatanya : New Filter
    if ($customer_id != ALL_TEXT)
        $sql .= " AND sorder.debtor_no = " . db_escape($customer_id);

    $sql .= " GROUP BY sorder.order_no,
			sorder.debtor_no,
			sorder.branch_code,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.deliver_to";
    return $sql;
}

function get_sql_for_hpp($selected_customer, $trans_type, $trans_no, $filter, $stock_item = null,
    $from = '', $to = '', $ref = '', $location = '', $customer_id = ALL_TEXT)
{

    $hpp_sql = "SELECT * FROM HPP";
    $hpp_res = db_query($hpp_sql);
    $hpp = db_num_rows($hpp_res) > 0;

    $sql = "SELECT
			line.id,
			line.ref,
			sorder.reference,
			debtor.name,
			branch.br_name,
			sorder.ord_date,
					sorder.delivery_date,
					Sum((line.unit_price*line.quantity)-line.discount_percent)+freight_cost AS OrderValue,
					sorder.type,
					debtor.curr_code,
					Sum(line.qty_sent) AS TotDelivered,
					Sum(line.quantity) AS TotQuantity,
					line.isclose
					FROM " . TB_PREF . "sales_orders as sorder, " . TB_PREF .
        "sales_order_details as line, " . TB_PREF . "debtors_master as debtor, " .
        TB_PREF . "cust_branch as branch
					WHERE sorder.order_no = line.order_no                    
					AND sorder.status = 1
					AND sorder.trans_type = line.trans_type
					AND sorder.trans_type = " . db_escape($trans_type) . "
					AND sorder.debtor_no = debtor.debtor_no
					AND sorder.branch_code = branch.branch_code
					AND debtor.debtor_no = branch.debtor_no";

    if ($hpp)
    {
        $sql .= " AND line.ref NOT IN(SELECT no_nota from hpp)";
    }

    if (isset($trans_no) && $trans_no != "")
    {
        // search orders with number like
        $number_like = "%" . $trans_no;
        $sql .= " AND sorder.order_no LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } elseif ($ref != "")
    {
        // search orders with reference like
        $number_like = "%" . $ref . "%";
        $sql .= " AND sorder.reference LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    } else // ... or select inquiry constraints
    {
        if ($filter != 'DeliveryTemplates' && $filter != 'InvoiceTemplates' && $filter !=
            'OutstandingOnly')
        {
            $date_after = date2sql($from);
            $date_before = date2sql($to);

            $sql .= " AND sorder.ord_date >= '$date_after'" . " AND sorder.ord_date <= '$date_before'";
        }
    }


    if ($trans_type == ST_SALESQUOTE && !check_value('show_all'))
        $sql .= " AND sorder.delivery_date >= '" . date2sql(Today()) .
            "' AND line.qty_sent=0"; // show only outstanding, not realized quotes

    if ($selected_customer != -1)
        $sql .= " AND sorder.debtor_no=" . db_escape($selected_customer);

    if (isset($stock_item))
        $sql .= " AND line.stk_code=" . db_escape($stock_item);

    if ($location)
        $sql .= " AND sorder.from_stk_loc = " . db_escape($location);

    if ($filter == 'OutstandingOnly')
        $sql .= " AND line.qty_sent < line.quantity";

    elseif ($filter == 'InvoiceTemplates' || $filter == 'DeliveryTemplates')
        $sql .= " AND sorder.type=1";

    //Chaiatanya : New Filter
    if ($customer_id != ALL_TEXT)
        $sql .= " AND sorder.debtor_no = " . db_escape($customer_id);

    $sql .= " GROUP BY sorder.order_no,
			sorder.debtor_no,
			sorder.branch_code,
			sorder.customer_ref,
			sorder.ord_date,
			sorder.deliver_to";
    return $sql;

}

?>